﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace CMS {
    public class CMS_DB {
        string connectionString;
        SqlConnection cmdbConnection;           //connection

        public CMS_DB() {
            connectionString = "Data Source=" + service.dbAccess.server + @"\" + service.dbAccess.instance + ";" 
                + "Initial Catalog=" + service.dbAccess.dbname + ";" +
                "User ID=" + service.dbAccess.login + ";" +
                "Password=" + service.dbAccess.pwd;

            cmdbConnection = new SqlConnection(connectionString);
        }

        /*
         * checks if user exists in database
         * 
         */
        public bool isUserValidExists(string username, string password){
            int exists = 0;

            string query = @"select count(Uname) from CmsUser where   Uname = '"
                    + username + @"' and Upassword= '" + password + @"';";

            try {
                cmdbConnection.Open();                    

                SqlCommand cmd = new SqlCommand(query, cmdbConnection);
                exists = (int)cmd.ExecuteScalar();

            } catch (InvalidOperationException ioe){
                throw ioe;

            } catch(SqlException sq){
                throw sq;

            } finally {
                cmdbConnection.Close();                    
            }

            if (exists == 1)
                return true;
            else
                return false;
        }
       
        
        /*
         * returns userID by username.
         * Can be useful for database performance
         */
        public int getUserIdByUsername(string username) {
            int userID = 0;

            string query = @"select UID from CmsUser where Uname = '" + username + @"';";

            try {
                cmdbConnection.Open();

                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                if (!rdr.Read())
                    return -1;              //should not happen (strange comment btw)
                else
                    userID = (int)rdr["UID"];

            } catch (InvalidOperationException ioe) {
                throw ioe;

            } catch (SqlException sq) {
                throw sq;

            } finally {
                cmdbConnection.Close();
            }
            return userID;
        }
        /*
         * returnas list of users
         */
        public List<string> getListOfUsers() {
            string query = "select Uname from CmsUser";
            List<string> result = new List<string>();

            try {
                cmdbConnection.Open();
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);
                SqlDataReader rdr = cmd.ExecuteReader();
                while (!rdr.Read()) {
                    result.Add( (string)rdr["Uname"] );
                }                   

            } catch (InvalidOperationException ioe) {
                return result;
            } catch (SqlException sq) {
                return result;
            } finally {
                cmdbConnection.Close();
            }
            return result;
        }
        /*
         * returns Change Requerst 
         */
        public CRArray getCRByID(long crid) {
            
            string query = @"select * from ChangeRequest where CRID = " + crid + @";";

            CRArray result = getListOfCrs(query);
            return result;
        }
        /*
         * 

        /*
         * returns all requests, opened by %username%
         * closed requests are not returned
         * ACCESS: CI
         */
        public CRArray getCRsByCI(long userid, bool getOpen) {

            string query = @"select * from ChangeRequest where CRinitiator  = " + userid;
            if (getOpen)
                query += " and CRIsClosed = 0 ";
            else
                query += " and CRIsClosed = 1 ";

            CRArray result = getListOfCrs(query);
            return result;
        }

        /*
         * returns all requests, opened by %username%
         * closed requests are not returned
         * ACCESS: CI
         */
        public CRArray getClosedCRsByCI(string username) {

            string query = @"select * from ChangeRequest where CRinitiator  = (select UID from CmsUser where Uname = '" + username + "' and CRIsClosed = 1 )";

            CRArray result = getListOfCrs(query);
            return result;
        }

        /*
         * creates change request
         * may not work in multi-instance environment
         */
        public long createChangeRequest(long initiator, string request)
        {
            string updateQ = @"insert into ChangeRequest (CRInitiator, CRInitRequest, CRImplDateShed) output inserted.crid values (" + initiator + @", '" + request + @"', '1900/01/01')";

            long resultID;
            resultID = insertIntoCMDB(updateQ);

            return resultID;
        }

        

        /*
         * Lists all CR's withoout CM assigned to it
         * ACCESS: CM AB ONLY
         */
        public CRArray getCRsWithoutCM() {

            string query = @"select * from ChangeRequest where CRManagerUID is NULL;";

            CRArray result = getListOfCrs(query);

            return result;
        }

        /*
         * lists owned CR's by particular CM
         * output parameters:
         * 
         * ACCESS: CM AB only
         */
        public CRArray getCRsByCM(long cmID, bool getEvaluated, bool getOpen ) {
            
                string query = @"select * from ChangeRequest where CRManagerUID = " + cmID + @" ";
            if(getEvaluated)
                query += @"and CREvaluatedByCm = 1 ";
            else
                query += @"and CREvaluatedByCm = 0 ";

            if (getOpen)
                query += @" and CRIsClosed = 0";
            else
                query += @" and CRIsClosed = 1";

            CRArray result = getListOfCrs(query);

            return result;                    
        }

        

        /*
         * assignes CM to CR
         * ACCESS: CB AB
         */
        public long assignCMtoCR(long cmID, long crid)
        {
            string query = @"update ChangeRequest set CRManagerUID = '" + cmID + @"' output INSERTED.crid where CRID = '" + crid + @"';";

            long result = this.insertIntoCMDB(query);
            return result;
        }


        /*
         * take ownership on all requests without CM
         */
        public bool assignCMtoAllCRs(long cmID) {
            string query = @"update ChangeRequest set CRManagerUID = '" + cmID + @"' where CRManagerUID is null;";
            long? answer = null;

            try {
                cmdbConnection.Open();
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);
                answer = (int)cmd.ExecuteNonQuery();
                cmd.Dispose();
            } catch (InvalidOperationException) {
                return false;
            } catch (InvalidCastException) {
                return false;
            } catch (SqlException) {
                return false;
            } finally {
                cmdbConnection.Close();
            }
                        
            if (answer == null)
                 return false;
            else 
                return true;
        }


        /*
         * Updates data of CR after CM processes requst and prepares it for AB
         * ACCESS: CM
         */
        public long evaluateCRfromCM(ChangeRequest cr) {

            string query = @"update ChangeRequest set CRInitRequest = '" + cr.request + @"',  CRImpactEstim = '" + cr.impactEstim + @"', CRCostEstim = '" + cr.costEstim + @"', CREvaluatedByCM = 1  output INSERTED.crid where CRID = " + cr.ID + @";";

            long affectedCR = this.insertIntoCMDB(query);
            return affectedCR;
        }


        /*
         * allows CI to edit his request
         * ACCESS: CI
         */
        public long updateCRfromCI(ChangeRequest cr) {

            string query = @"update ChangeRequest set CRInitRequest = '" + cr.request + @"' output INSERTED.crid where CRID = " + cr.ID + @";";

            long affectedCrId = this.insertIntoCMDB(query);
            return affectedCrId;
        }
        
        /*
         * 
         * Assigns computer to CR
         * ACCESS: CM
         */
        public bool assignComputerAffectedToCR(long crid, string compid) {
            string query = @"insert into CR_Comp(CRC_CompID, CRC_CRID) values ('" + compid + @"', " + crid + @")";

            if (!executeInsertQuery(query))
                return false;
            else
                return true;             
        }

        /*
         * Shows list of computers, affected by CR
         * ACCESS: AB , CM, CIM
         */
        public void getComputersForCR(long crid) {
            string query = @"select Computer.* from CR_Comp, Computer
where CR_Comp.CRC_CompID = Computer.compid and CR_Comp.CRC_CRID =" + crid + @";";

        }

        /*
         * assigns Software to CR
         * ACCESS: CM
         */
        public bool asignSoftwareAffectedToCR(long crid, long softid) {
            string query = @"insert into CR_Soft(CRS_SoftID, CRS_CRID) values(" + softid + @"," + crid + @")";

            if (!executeInsertQuery(query))
                return false;
            else
                return true;   
        }



        public void getSoftwaresForCR(long crid) {
            string query = @"select software.* from cr_soft, software
where cr_soft.crs_crid = " + crid + "and cr_soft.crs_softid = software.softid";
        }


        /*
         * assigns PT to CR
         * ACCESS: CM
         */
        public bool assignPTAfefectedToCr(long crid, long ptid) {
            string query = @"insert into CR_PTC(CRPTC_PTID, CRPTC_CRID) values (" + ptid + @", " + crid + @" )";

            if (!executeInsertQuery(query))
                return false;
            else
                return true;   
        }
        
        public SoftwareArray getSoftByID(long softID) {
            string query = @"Select * from software where softid = " + softID.ToString();
            SoftwareArray sa = this.getListOfSoftwares(query);
            return sa;
        }

        public PTArray getPTbyID(long ptID) {
            string query = @"Select * from PlainText where ptid = " + ptID.ToString();
            PTArray pta = this.getListOfPlainTextConfigs(query);
            return pta;
        }

        public PSArray getPSbyID(long psID) {
            string query = @"Select * from PartialState where psid = " + psID.ToString();
            PSArray psa = this.getListOfPartialStateConfigs(query);
            return psa;
        }

        public SoftType getSoftTypeByID(long softtypeID) {
            string query = @"Select * from SoftType where SoftTypeid =" + softtypeID.ToString();
            SoftType st = this.getSoftType(query);
            return st;
        }


        public void getPTbyCR(long crid) {
            string query = @"select plainTextconfig.* from CR_PTC, plaintextconfig
where cr_ptc.crptc_crid = " + crid + @" and cr_ptc.crptc_ptid = plaintextconfig.ptid";
        }


        /*
         * assigns PS to CR
         * ACCESS: CM
         */
        public bool assignPSAffectdToCR(long crid, long psid) {
            string query = @"insert into cr_ps(crps_psid, crps_crid) values ("+ psid + @", "+ crid +@")";
            if (!executeInsertQuery(query))
                return false;
            else
                return true;             
        }



        public void getPSbyCR(long crid) {
            string query = @"select  partialstate.* from partialstate, cr_ps where cr_ps.crps_crid = " + crid + @" and cr_ps.crps_psid = partialstate.psid";

        }

        /*
         * addnew pc to user
         * ACCESS: EM, CIM
         */
        public string addNewComputer(Computer pc) {
            
            string query;

            query = @"insert into Computer(CompID, CompLocation, UID, CompDateBought, CompComment, CompHardwareDescr) output INSERTED.compid values ('"
            + pc.ID + @"', '"
            + pc.location + @"',"
            + pc.userID + @", '"
            + pc.dateBought + @"', '"
            + pc.comment + @"' , '"
            + pc.hwdescription + @"')";
            

            string result = this.insertStringIntoCMDB(query);
            return result; 
        }





        public bool assignEnterpriseUserToComputer(string compid, string userid) {
                string query = @" update... ";
            

                return true;   
        }

        /*
         * add new OS
         * all parameters should be already valid
         * ACCESS: CM EM
         */
        public long addNewOS(OperatingSystem os) {
            string query = @"insert into OS (OsCompId, OsHostName, OsSoftTypeid, OsLicID) output INSERTED.osid values ('" + os.compID + @"','" 
                + os.hostname + "',"
                + os.softType + "," 
                + os.licenseID + ")";


            long result = this.insertIntoCMDB(query);
            return result;         
        }


        /*
         * adds new software
         * ACCESS: CM EM
         */
        public long addNewSoftware(Software soft) {
            string query = @"insert into software (SoftOsID, SoftTypeID, SoftPrevVerID, SoftLicID) output INSERTED.softid values (" + soft.osID + "," + soft.softTypeID + "," + soft.prevVerID + "," + soft.licID + ")";

            long result = this.insertIntoCMDB(query);

            return result;
        }


        /*
         * adds new software type
         * returns 
         * 1  -success
         * 0 - processing error
         * -1 - such combination exists in database
         * ACCESS: CM EM
         */
        public long addNewSoftwareType(SoftType st) {
            if(isSoftwareTypeExists(st.SoftName, st.SoftVer))
                return -1;
            else{
                string query = @"insert into SoftType ( SoftName, SoftVer) output INSERTED.SoftTypeID values ( '" + st.SoftName + @"', '" + st.SoftVer + @"')";

                long result = this.insertIntoCMDB(query);
                return result;
            }
        }


       

        /*
         * adds new licence into database
         * ACCESS: CM EM
         */
        public long addNewLicence(license lc) {
            string query = @"insert into License(LicNo, licexpdate, softtype, licamount) output INSERTED.licid values ('" + lc.licNO + @"', '" 
          + lc.expDate + @"', " 
          + lc.softType + @"," 
          + lc.amount + @")";

            long result = this.insertIntoCMDB(query);
            return result;
        }

        

        /*
         * creates new user as CI only
         * ACCESS: CM EM
         * returns
         * 1 - success
         * 0 - database error
         * -1 - user already exists
         */        
        public long addNewEnterpriseUser(string user, string pwd) {
            if(isUserExists(user ))
                return -1;

            string query = @"insert into CmsUser(uname, upassword) output INSERTED.uid
values ('" + user  + @"', '" + pwd + "')";

            long result = this.insertIntoCMDB(query);
            return result;
        }
        
        
        

        /* 
         * adds new plain Text config into CMS
         * ACCESS; CM EM
         */
        public long addNewPT(PlainTextConfig pt) {
            string query = @"insert into PlainText(ptsoftid, ptprevverid, ptconfig, ptcomment, ptfilesysloc) output INSERTED.ptid values (" 
            + pt.softwareID + @", " 
            + pt.prevVerID + @" , '" 
            + pt.configText + @"', '" 
            + pt.comment + @"', '"
            + pt.filesysLoc + @"')";

            long result = this.insertIntoCMDB(query);
            return result;
        }

        /*
         * adds new Partial State to a software
         * ACCESS : CM EM
         */
        public long addNewPS(PartialStateConfig ps) {
            string query = @"insert into partialstate(pssoftid, psscriptapplied, psstate, pscomment, psprevstate, psfilesysaddr) output INSERTED.psid values ("
            + ps.ID + @", '" 
            + ps.scriptApplied + @"', '" 
            + ps.stateAferScript + @"', '" 
            + ps.comment + @"'," 
            + ps.prevVerID + @" , ' " 
            + ps.filesysLoc + @"')";

            long result = this.insertIntoCMDB(query);
            return result;             
        }




        //list of id's only
        public DataSet getListOfCompIDs() {
            string query = @"select compid from Computer";
            DataSet ds = new DataSet();

            try {
                cmdbConnection.Open();
                SqlDataAdapter q = new SqlDataAdapter(query, cmdbConnection);
                q.Fill(ds, "ChangeRequest");
            } catch (InvalidOperationException ioe) {
                throw ioe;
            } catch (SqlException sq) {
                throw sq;
            } finally {
                cmdbConnection.Close();
            }
            return ds;
        }

        /*
         * returns computer profile by computer ID
         * ACCESS: not CI
         */
        public ComputerArray getComputerByID(string compid) {
            ComputerArray answer = new ComputerArray();

            string query = @"select * from Computer where compid = '" + compid + @"'";

            answer = this.getListOfComputers(query);
            return answer;
        }
        /*
         * returns full list of OS profiles, installed on specified computer
         * 
         */
        public OSArray getOSByCompID(string compid) {
            OSArray answer = new OSArray();

            string query = @"select * from OS where oscompid = '" + compid + @"'";
            answer = this.getListOfOperatingSystems(query);
            return answer;
        }


        public licenseArray getLicenceByID(long licID) {
            string query = @"Select * from License where licid = " + licID.ToString();
            licenseArray answer = this.getListOsLicences(query);
            return answer;
        }

        public OSArray getOSByOsID(long osid) {
            string query = @"Select * from os where osid =" + osid.ToString();
            OSArray answer = this.getListOfOperatingSystems(query);
            return answer;
        }
        /*
         * Returns full list of software profiles, installed on specified OS
         */
        public SoftwareArray getSoftByOSID(long osid) { 
            SoftwareArray result = new SoftwareArray();
            string query = @"select * from Software where SoftOsId = " + osid.ToString() ;
            result = this.getListOfSoftwares(query);
            return result;
        }
        /*
         * returns list of plain text configs, by software ID
         */
        public PTArray getPTBySoftID(long softID) {
            PTArray answer = new PTArray();
            string query = @"select * from PlainText where PtSoftID = " + softID.ToString();
            answer = this.getListOfPlainTextConfigs(query);
            return answer;
        }
        /*
         * returns list of partial state configs by software ID
         */
        public PSArray getPSBySoftID(long softID) {
            PSArray answer = new PSArray();
            string query = @"select * from partialState where PsSoftID = " + softID.ToString();
            answer = this.getListOfPartialStateConfigs(query);
            return answer;
        }

        /*
         * complex method, which lists CRs with dependencies
         */
        public CRWithDependencies getCRWithItemsAffected(long CRID) {
            CRWithDependencies result = new CRWithDependencies();

            //queries
            string computers = @"select * from Computer where compID in (select crc_compid from CR_Comp where crc_crid = " + CRID.ToString() + @") order by compID asc";

            string softwares = @"select * from Software where SoftID in (select crs_softid from CR_Soft where crs_crid = " + CRID.ToString() + @") order by softid asc;";

            string PTs = @"select * from PlainText where PtID in (select crptc_ptid from CR_ptc where crptc_crid = " + CRID.ToString() + @") order by ptid asc";

            string PSs = @"select * from PartialState where PsID in (select crps_psid from CR_ps where crps_crid = " + CRID.ToString() + @") order by psid asc";


            result.CRRequested = this.getCRByID(CRID);
            result.ComputersAffected = this.getListOfComputers(computers);
            result.SoftwaresAffected = this.getListOfSoftwares(softwares);
            result.PTAffected = this.getListOfPlainTextConfigs(PTs);
            result.PSAffected = this.getListOfPartialStateConfigs(PSs);

            return result;
        }
        /*
         * complex method, returning CR and all Approves
         */
        public CRWithApproves getCRWithApproves(long crID) {
            CRWithApproves CRAppr = new CRWithApproves();

            //get Change request
            CRAppr.CRRequested = this.getCRByID(crID);

            string getApproves = @"Select * from CRApprove where crapprovedCRid = " + crID.ToString();

            //get approves to it
            CRAppr.approves = this.getListOfApproves(getApproves);
            return CRAppr;
        }
        
        //do not implement on current version
        #region equipment update functions. 

        public bool udateComputer(string compid) {
            return true;
        }

        public bool updateOS(long osid) {
            return true;
        }

        public bool updateSoftware(long softid) { return true; }

        public bool updatePT(long ptid) { return true; }

        public bool updatePS(long ptid) { return true; }

        public bool updateUserPassword(string userid, string newPassword) { return true; }

        #endregion

        //====================================================================
        #region AB operations
        /*
         * Approves or disproves CR
         * ACCESS: AB
         */
        public long approveCRfromAB(Approve appr) {
            string query = @"insert into CRApprove(CRApproverUID, CRApprovedCRID,  crisapproved, crcomment)  output INSERTED.crapprid values ("
                + appr.UserID + @", " 
                + appr.ApprovedCR + @", " 
                + Convert.ToInt32( appr.isApproved) + @", '" 
                + appr.comment + @"')";

            long result = this.insertIntoCMDB(query);
            return result;
        }

        /*
         * returns unapproved open CR's
         * ACCESS: AB
         */
        public CRArray getCRsUnapprovedByAB(long ABuserid) {
            //long abUserName = this.getUserIdByUsername(ABuserid);

            string query = @"select c.* from ChangeRequest as c where crevaluatedbycm = 1 and crisclosed = 0 	and not exists (select * from crapprove where crapproveruid = " + ABuserid + @" and crapprovedcrid = c.crid) ";

            CRArray result =  getListOfCrs(query);
            return new CRArray();
        }
        /*
         * returns list of fully approved CR's
         */
        public CRArray getFullyApprovedCRs(int abMembers) {
            CRArray candidates = this.getListOfEvaluatedNotClosedNotImplementedRequests();
            string checkQuery = @"select count(*) from CRApprove where crapprovedcrid =";

            foreach (ChangeRequest cr in candidates.list) {
                string tempQ = checkQuery + cr.ID.ToString();
                if(this.insertIntoCMDB(tempQ) < abMembers)
                    candidates.list.Remove(cr);                
            }
            return candidates;
        }
        
         
        public CRArray getListOfEvaluatedNotClosedNotImplementedRequests() {
            string query = @"select * from ChangeRequest where crevaluatedbycm = 1 and crisclosed=0 and crisimplemented=0";
            CRArray result = this.getListOfCrs(query);
            return result;
        }
        


        /*
         * after all aB members approved request
         */
        public long sheduleCRAndAssignCIM(ChangeRequest cr) {
            string query = @"update ChangeRequest set CRImplDateShed='" + cr.implementation + "', CrCIMUID= " + cr.cim + " output INSERTED.crid where crid =" + cr.ID;
            long result = this.insertIntoCMDB(query);
            return result;
        }


        /*
         * after it was implemented or after it was declined by AB
         */
        public long setCrClosedAndImplemented(long crid) {
            string query = @"update ChangeRequest set CRIsClosed = 1, crIsImplemented = 1 where crid = " + crid.ToString() + "output INSERTED.crid";
            long result = this.insertIntoCMDB(query);
            return result;
        }

        public long setCrClosedAndNotImplemented(long crid) {
            string query = @"update ChangeRequest set CRIsClosed = 1, crIsImplemented = 0 where crid = " + crid.ToString() + "output INSERTED.crid";
            long result = this.insertIntoCMDB(query);
            return result;
        }

        #endregion

        //=======================================================================
        #region CIM functions
        /*
         * watch which cr's set to be implemented
         * ACCESS: CIM
         */
        public CRArray getApprovedNotImplementerCRsByCIM(long cim){
            int ab = service.accessRights.adviceBoard.Count();
            string query = @"select * from ChangeRequest as cr where ((select count(*) from CRApprove where rapprovedcrid = cr.crid) = " + ab + @") and crisclosed=0 and crisimplemented=0 and crcimuid =" + cim.ToString();
            
            CRArray candidates = this.getListOfCrs(query);
            return candidates;
        }

        /*
         * mark CR as implemented
         */
        public long setCRasImplemented(long crid) {
            string query = @"update ChangeRequest set crisclosed = 1 where crid = " + crid.ToString() + "output INSERTED.crid";
            long upd = this.insertIntoCMDB(query);
            return upd;
        }


        #endregion


        //=======================================================================
        #region internal functions
        /*
         * checks if user already exists in database
         * returns true, if yes
         */
        private bool isUserExists(string username) {
            int exists = 0;

            string query = @"select count(Uname) from CmsUser where   Uname = '"
                    + username + @"';";

            try {
                cmdbConnection.Open();
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);
                exists = (int)cmd.ExecuteScalar();

            } catch (InvalidOperationException ioe) {
                throw ioe;

            } catch (SqlException sq) {
                throw sq;

            } finally {
                cmdbConnection.Close();
            }

            if (exists == 1)
                return true;
            else
                return false;
        }
        
        //does insert on a ready query
        private bool executeInsertQuery(string query) {
            try {
                cmdbConnection.Open();
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);
                cmd.ExecuteNonQuery();
            } catch (InvalidOperationException) {
                return false;
            } catch (SqlException) {
                return false;
            } finally {
                cmdbConnection.Close();
            }
            return true;
        }

        

        /*
        * checks if current software type is already in the database
        */
        private bool isSoftwareTypeExists(string softname, string version) {
            string query = @"select count(*) from SoftType where SoftName = '" + softname + @"' and softver = '" + version + @"'";
            int exists = 0;

            try {
                cmdbConnection.Open();

                SqlCommand cmd = new SqlCommand(query, cmdbConnection);
                exists = (int)cmd.ExecuteScalar();

            } catch (InvalidOperationException ioe) {
                throw ioe;

            } catch (SqlException sq) {
                throw sq;

            } finally {
                cmdbConnection.Close();
            }
            if (exists == 0)
                return false;
            else
                return true;
        }

        /*
         * queries CMDB and returns a list of CR's according the query
         * or returns an error
         */
        private CRArray getListOfCrs(string query) {
            
            ChangeRequest cr;
            CRArray cra = new CRArray();
            try {
                cmdbConnection.Open();
                 
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();                

                while (rdr.Read()) {
                    cr = new ChangeRequest();
                    cr.ID = (long)rdr["CRID"];
                    cr.initiator = (int)rdr["CRInitiator"];
                    cr.request = (string)rdr["CRInitRequest"];
                    if (rdr.IsDBNull(rdr.GetOrdinal("CRManagerUID")))
                        cr.cm = null;
                    else
                        cr.cm = (int)rdr["CRManagerUID"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("CRImpactEstim")))
                        cr.impactEstim = null;
                    else
                        cr.impactEstim = (string)rdr["CRImpactEstim"];

                    if(rdr.IsDBNull(rdr.GetOrdinal("CRCostEstim")))
                        cr.costEstim = null;
                    else
                        cr.costEstim = (string)rdr["CRCostEstim"];

                    cr.isEvaluated = (bool)rdr["CREvaluatedByCM"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("CRImplDateShed")))
                        cr.implementation = new DateTime(1900, 01, 01);
                    else
                        cr.implementation = (DateTime)rdr["CRImplDateShed"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("CRCimUID")))
                        cr.cim = null;
                    else
                        cr.cim = (int)rdr["CRCimUID"];

                    cr.isClosed = (bool)rdr["CRIsClosed"];
                    cr.isImplemented = (bool)rdr["CRIsImplemented"];

                    cra.addCRToArray(cr);
                }
            } catch (InvalidOperationException) {
                
            } catch (InvalidCastException){

            } catch (SqlException) {
                
            } finally {
                cmdbConnection.Close();
            }
            return cra;
        }

        

        private ComputerArray getListOfComputers(string query){
            Computer comp;
            ComputerArray compList = new ComputerArray();
            try {
                cmdbConnection.Open();
                 
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();                

                while (rdr.Read()) {
                    comp = new Computer();
                    
                    comp.ID = (string)rdr["CompID"];
                    comp.location = (string)rdr["CompLocation"];
                    if (rdr.IsDBNull(rdr.GetOrdinal("UID")))
                        comp.userID = null;
                    else
                        comp.userID = (int)rdr["UID"];
                    comp.dateBought = (DateTime)rdr["CompDateBought"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("CompComment")))
                        comp.comment = null;
                    else
                        comp.comment = (string)rdr["CompComment"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("CompHardwareDescr")))
                        comp.hwdescription = null;
                    else
                        comp.hwdescription = (string)rdr["CompHardwareDescr"];

                    compList.addNew(comp);
                }
            } catch (InvalidOperationException) {

            } catch (InvalidCastException) {

            } catch (SqlException) {

            } finally {
                cmdbConnection.Close();
            }
            return compList;
        }

        private SoftwareArray getListOfSoftwares(string query) {
            Software soft;
            SoftwareArray softList = new SoftwareArray();
            try {
                cmdbConnection.Open();
                
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read()) {
                    soft = new Software();

                    soft.ID = (long)rdr["SoftID"];
                    soft.osID = (long)rdr["SoftOsId"];
                    soft.softTypeID = (long)rdr["SoftTypeId"];
                    if (rdr.IsDBNull(rdr.GetOrdinal("SoftPrevVerID")))
                        soft.prevVerID = null;
                    else
                        soft.prevVerID = (long)rdr["SoftPrevVerID"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("SoftLicId")))
                        soft.licID = null;
                    else
                        soft.licID = (long)rdr["SoftLicId"];

                    softList.addNew(soft);

                }
            } catch (InvalidOperationException) {

            } catch (InvalidCastException) {

            } catch (SqlException) {

            } finally {
                cmdbConnection.Close();
            }
            return softList;
        }

        private licenseArray getListOsLicences(string query) {
            license lic;
            licenseArray licArr = new licenseArray();

            try {
                cmdbConnection.Open();
                 
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read()) {
                    lic = new license();

                    lic.ID = (long)rdr["LicID"];
                    lic.licNO = (string)rdr["LicNo"];
                    if (rdr.IsDBNull(rdr.GetOrdinal("LicEXPDate")))
                        lic.expDate = new DateTime(1900, 01, 01);
                    else
                        lic.expDate = (DateTime)rdr["LicEXPDate"];

                    lic.softType = (long)rdr["SoftType"];
                    lic.amount = (int)rdr["LicAmount"];

                    licArr.addNew(lic);
                }
            } catch (InvalidOperationException) {

            } catch (InvalidCastException) {

            } catch (SqlException) {

            } finally {
                cmdbConnection.Close();
            }
            return licArr;
        }

        private OSArray getListOfOperatingSystems(string query) {
            OperatingSystem os;
            OSArray osArr = new OSArray();
            try {
                cmdbConnection.Open();
                 
                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read()) {
                    os = new OperatingSystem();

                    os.ID = (long)rdr["OsID"];
                    if (rdr.IsDBNull(rdr.GetOrdinal("OsCompID")))
                        os.compID = null;
                    else
                        os.compID = (string)rdr["OsCompID"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("OsHostName")))
                        os.hostname = null;
                    else
                        os.hostname = (string)rdr["OsHostName"];

                    os.softType = (long)rdr["OsSoftTypeID"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("OsLicID")))
                        os.licenseID = null;
                    else
                        os.licenseID = (long)rdr["OsLicID"];

                    osArr.addNew(os);
                }
            } catch (InvalidOperationException) {

            } catch (InvalidCastException) {

            } catch (SqlException) {

            } finally {
                cmdbConnection.Close();
            }
            return osArr;
        }

        private PTArray getListOfPlainTextConfigs(string query) {
            PlainTextConfig pt;
            PTArray ptarr = new PTArray();
            try {
                cmdbConnection.Open();

                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read()) {
                    pt = new PlainTextConfig();

                    pt.ID = (long)rdr["PtID"];
                    pt.softwareID = (long)rdr["PtSoftID"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("PtPrevVerID")))
                        pt.prevVerID = null;
                    else
                        pt.prevVerID = (long)rdr["PtPrevVerID"];

                    pt.configText = (string)rdr["PtConfig"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("PtComment")))
                        pt.comment = null;
                    else
                        pt.comment = (string)rdr["PtComment"];

                    pt.filesysLoc = (string)rdr["PtFilesysLoc"];

                    ptarr.addNew(pt);
                }
            } catch (InvalidOperationException) {

            } catch (InvalidCastException) {

            } catch (SqlException) {

            } finally {
                cmdbConnection.Close();
            }
            return ptarr;
        }

        private PSArray getListOfPartialStateConfigs(string query) {
            PartialStateConfig ps;
            PSArray psarr = new PSArray();
            try {
                cmdbConnection.Open();

                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read()) {
                    ps = new PartialStateConfig();

                    ps.ID = (long)rdr["PsID"];

                    ps.softwareID = (long)rdr["PsSoftID"];

                    ps.scriptApplied = (string)rdr["PsScriptApplied"];

                    ps.stateAferScript = (string)rdr["PsState"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("PsComment")))
                        ps.comment = null;
                    else
                        ps.comment = (string)rdr["PsComment"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("PsPrevState")))
                        ps.prevVerID = null;
                    else
                        ps.prevVerID = (long)rdr["PsPrevState"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("PsFilesysAddr")))
                        ps.filesysLoc = null;
                    else
                        ps.filesysLoc = (string)rdr["PsFilesysAddr"];

                    psarr.addNew(ps);

                }
            } catch (InvalidOperationException) {

            } catch (InvalidCastException) {

            } catch (SqlException) {

            } finally {
                cmdbConnection.Close();
            }
            return psarr;
        }

        private SoftType getSoftType(string query) {
            SoftType st = new SoftType();
            try {
                cmdbConnection.Open();

                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read()) {
                    st.SoftName = (string)rdr["SoftName"];                    
                    st.SoftVer += (string)rdr["SoftVer"];
                }
            } catch (InvalidOperationException) {

            } catch (InvalidCastException) {

            } catch (SqlException) {

            } finally {
                cmdbConnection.Close();
            }
            return st;
        }

        private ApproveArray getListOfApproves(string query) {
            ApproveArray result = new ApproveArray();
            Approve appr;
            try {
                cmdbConnection.Open();

                SqlCommand cmd = new SqlCommand(query, cmdbConnection);

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read()) {
                    appr = new Approve();

                    appr.ID = (long)rdr["CRApprID"];
                    appr.UserID = (int)rdr["CRApproverUID"];
                    appr.ApprovedCR = (long)rdr["CRApprovedCRID"];
                    appr.isApproved = (bool)rdr["CRIsApproved"];

                    if (rdr.IsDBNull(rdr.GetOrdinal("CRComment")))
                        appr.comment = null;
                    else
                        appr.comment = (string)rdr["CRComment"];
                    appr.approveDate = (DateTime)rdr["CRApprDate"];

                    result.addNew(appr);
                }
            } catch (InvalidOperationException) {

            } catch (InvalidCastException) {

            } catch (SqlException) {

            } finally {
                cmdbConnection.Close();
            }
            return result;
        }
        /*
         * inserts any query into CMDB and returns ID to a new row
         * returns -1 on method error
         * -2 on database error
         */
        private long insertIntoCMDB(string updateQ) {
            long resultID;
            try {
                cmdbConnection.Open();
                SqlCommand cmd = new SqlCommand(updateQ, cmdbConnection);
                resultID = (long)cmd.ExecuteScalar();
                cmd.Dispose();

            } catch (InvalidOperationException) {
                return -1;
            } catch (InvalidCastException){
                return -1;
            } catch (SqlException) {
                return -2;
            } finally {
                cmdbConnection.Close();
            }
            return resultID;
        }
        /*
         * inserts request into CMDB
         * returns id of newly inserted item
         * 
         * CAN CAUSE FAILURES WITH MULTIPLE CONSUMERS!!!
         */
        private string insertStringIntoCMDB(string updateQ) {
            string q = "select max(compid) as compid from computer ;";
            string resultID;
            try {
                //implementing insert
                cmdbConnection.Open();
                SqlCommand cmd = new SqlCommand(updateQ, cmdbConnection);
                cmd.ExecuteNonQuery();

                //getting new value from database
                SqlCommand retCMD = new SqlCommand(q, cmdbConnection);
                SqlDataReader rdr = retCMD.ExecuteReader();
                rdr.Read();
                resultID = (string)rdr["compid"];
                cmd.Dispose();

            } catch (InvalidOperationException) {
                return "-1";
            } catch (InvalidCastException) {
                return "-1";
            } catch (SqlException) {
                return "-2";
            } finally {
                cmdbConnection.Close();
            }
            return resultID;
        }
        #endregion

        public bool isDBrequestSuccessful(long result){
            return true;
        }


        
    }
}
